﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Installer
{
    class SQLHelper
    {
        public static string CREATE_DATABASE = "USE [master]\n" +
            "CREATE DATABASE [DbSim] ON  PRIMARY \n" +
            "( NAME = N'DbSim', FILENAME = N'C:\\Program Files\\Microsoft SQL Server\\DbSim.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )\n" +
            " LOG ON \n" +
            "( NAME = N'DbSim_log', FILENAME = N'C:\\Program Files\\Microsoft SQL Server\\DbSim_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%);";

        public static string EXECUTE_DB_SETUP =
            "IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))\n" +
            "begin\n" +
            "EXEC [DbSim].[dbo].[sp_fulltext_database] @action = 'enable'\n" +
            "end ;" +

            "ALTER DATABASE [DbSim] SET ANSI_NULL_DEFAULT OFF \n" +
            ";\n" +
            "ALTER DATABASE [DbSim] SET ANSI_NULLS OFF \n" +
            ";\n" +
            "ALTER DATABASE [DbSim] SET ANSI_PADDING OFF \n" +
            ";\n" +
            "ALTER DATABASE [DbSim] SET ANSI_WARNINGS OFF \n" +
            ";\n" +
            "ALTER DATABASE [DbSim] SET ARITHABORT OFF \n" +
            ";\n" +
            "ALTER DATABASE [DbSim] SET AUTO_CLOSE ON \n" +
            ";\n" +
            "ALTER DATABASE [DbSim] SET AUTO_CREATE_STATISTICS ON \n" +
            ";\n" +
            "ALTER DATABASE [DbSim] SET AUTO_SHRINK OFF \n" +
            ";\n" +
            "ALTER DATABASE [DbSim] SET AUTO_UPDATE_STATISTICS ON \n" +
            ";\n" +
            "ALTER DATABASE [DbSim] SET CURSOR_CLOSE_ON_COMMIT OFF \n" +
            ";\n" +
            "ALTER DATABASE [DbSim] SET CURSOR_DEFAULT  GLOBAL \n" +
            ";\n" +
            "ALTER DATABASE [DbSim] SET CONCAT_NULL_YIELDS_NULL OFF \n" +
            ";\n" +
            "ALTER DATABASE [DbSim] SET NUMERIC_ROUNDABORT OFF \n" +
            ";\n" +
            "ALTER DATABASE [DbSim] SET QUOTED_IDENTIFIER OFF \n" +
            ";\n" +
            "ALTER DATABASE [DbSim] SET RECURSIVE_TRIGGERS OFF \n" +
            ";\n" +
            "ALTER DATABASE [DbSim] SET  DISABLE_BROKER \n" +
            ";\n" +
            "ALTER DATABASE [DbSim] SET AUTO_UPDATE_STATISTICS_ASYNC OFF \n" +
            ";\n" +
            "ALTER DATABASE [DbSim] SET DATE_CORRELATION_OPTIMIZATION OFF \n" +
            ";\n" +
            "ALTER DATABASE [DbSim] SET TRUSTWORTHY OFF \n" +
            ";\n" +
            "ALTER DATABASE [DbSim] SET ALLOW_SNAPSHOT_ISOLATION OFF \n" +
            ";\n" +
            "ALTER DATABASE [DbSim] SET PARAMETERIZATION SIMPLE \n" +
            ";\n" +
            "ALTER DATABASE [DbSim] SET READ_COMMITTED_SNAPSHOT OFF \n" +
            ";\n" +
            "ALTER DATABASE [DbSim] SET HONOR_BROKER_PRIORITY OFF \n" +
            ";\n" +
            "ALTER DATABASE [DbSim] SET RECOVERY SIMPLE \n" +
            ";\n" +
            "ALTER DATABASE [DbSim] SET  MULTI_USER \n" +
            ";\n" +
            "ALTER DATABASE [DbSim] SET PAGE_VERIFY CHECKSUM  \n" +
            ";\n" +
            "ALTER DATABASE [DbSim] SET DB_CHAINING OFF \n" +
            ";\n" +
            "USE [DbSim]\n" +
            ";\n" +
            "SET ANSI_NULLS ON\n" +
            ";\n" +
            "SET QUOTED_IDENTIFIER ON\n" +
            ";\n" +
            "CREATE TABLE [dbo].[Actions](\n" +
            "      [Id] [int] NOT NULL,\n" +
            "      [ActionName] [nvarchar](50) NOT NULL,\n" +
            "      [MathStringQ] [nvarchar](50) NULL,\n" +
            "      [MathStringT] [nvarchar](50) NULL,\n" +
            "PRIMARY KEY CLUSTERED \n" +
            "(\n" +
            "      [Id] ASC\n" +
            ")WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]\n" +
            ") ON [PRIMARY]\n" +
            "\n" +
            ";\n" +
            "SET ANSI_NULLS ON\n" +
            ";\n" +
            "SET QUOTED_IDENTIFIER ON\n" +
            ";\n" +
            "CREATE TABLE [dbo].[Cate;ry](\n" +
            "      [Id] [int] NOT NULL,\n" +
            "      [Cate;ryName] [nvarchar](50) NULL,\n" +
            "PRIMARY KEY CLUSTERED \n" +
            "(\n" +
            "      [Id] ASC\n" +
            ")WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]\n" +
            ") ON [PRIMARY]\n" +
            "\n" +
            ";\n" +
            "SET ANSI_NULLS ON\n" +
            ";\n" +
            "SET QUOTED_IDENTIFIER ON\n" +
            ";\n" +
            "CREATE TABLE [dbo].[Parameter](\n" +
            "      [Id] [int] NOT NULL,\n" +
            "      [ParamName] [nvarchar](50) NULL,\n" +
            "PRIMARY KEY CLUSTERED \n" +
            "(\n" +
            "      [Id] ASC\n" +
            ")WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]\n" +
            ") ON [PRIMARY]\n" +
            "\n" +
            ";\n" +
            "SET ANSI_NULLS ON\n" +
            ";\n" +
            "SET QUOTED_IDENTIFIER ON\n" +
            ";\n" +
            "CREATE TABLE [dbo].[Resources](\n" +
            "      [Id] [int] NOT NULL,\n" +
            "      [ResourceName] [nvarchar](50) NULL,\n" +
            "PRIMARY KEY CLUSTERED \n" +
            "(\n" +
            "      [Id] ASC\n" +
            ")WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]\n" +
            ") ON [PRIMARY]\n" +
            "\n" +
            ";\n" +
            "SET ANSI_NULLS ON\n" +
            ";\n" +
            "SET QUOTED_IDENTIFIER ON\n" +
            ";\n" +
            "CREATE TABLE [dbo].[Summary](\n" +
            "      [Id] [int] NOT NULL,\n" +
            "      [RecordId] [int] NULL,\n" +
            "      [Cate;ryId] [int] NULL,\n" +
            "      [ResourceId] [int] NULL,\n" +
            "      [ParamId] [int] NULL,\n" +
            "      [ActionId] [int] NULL,\n" +
            "      [Value] [float] NULL,\n" +
            "      [Notes] [nvarchar](50) NULL,\n" +
            "PRIMARY KEY CLUSTERED \n" +
            "(\n" +
            "      [Id] ASC\n" +
            ")WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]\n" +
            ") ON [PRIMARY]\n" +
            "\n" +
            ";\n" +
            "INSERT [dbo].[Actions] ([Id], [ActionName], [MathStringQ], [MathStringT]) VALUES (1, N'Менеджер', N'качество', N'время')\n" +
            "INSERT [dbo].[Actions] ([Id], [ActionName], [MathStringQ], [MathStringT]) VALUES (2, N'Специалист по требованиям ', N'качество', N'время')\n" +
            "INSERT [dbo].[Actions] ([Id], [ActionName], [MathStringQ], [MathStringT]) VALUES (3, N'Руководитель группы тестирования ', N'качество', N'время')\n" +
            "INSERT [dbo].[Actions] ([Id], [ActionName], [MathStringQ], [MathStringT]) VALUES (4, N'Руководитель группы тестирования ', NULL, NULL)\n" +
            "INSERT [dbo].[Actions] ([Id], [ActionName], [MathStringQ], [MathStringT]) VALUES (5, N'Разработчик', NULL, NULL)\n" +
            "INSERT [dbo].[Actions] ([Id], [ActionName], [MathStringQ], [MathStringT]) VALUES (6, N'Проектировщик', NULL, NULL)\n" +
            "INSERT [dbo].[Actions] ([Id], [ActionName], [MathStringQ], [MathStringT]) VALUES (7, N'Тестировщик', NULL, NULL)\n" +
            "INSERT [dbo].[Actions] ([Id], [ActionName], [MathStringQ], [MathStringT]) VALUES (8, N'Проектировщик тестов ', NULL, NULL)\n" +
            "INSERT [dbo].[Actions] ([Id], [ActionName], [MathStringQ], [MathStringT]) VALUES (9, N'Дизайнер', NULL, NULL)\n" +
            "INSERT [dbo].[Actions] ([Id], [ActionName], [MathStringQ], [MathStringT]) VALUES (10, N'Переводчик', NULL, NULL)\n" +
            "INSERT [dbo].[Cate;ry] ([Id], [Cate;ryName]) VALUES (1, N'Анализ')\n" +
            "INSERT [dbo].[Cate;ry] ([Id], [Cate;ryName]) VALUES (2, N'Управление')\n" +
            "INSERT [dbo].[Cate;ry] ([Id], [Cate;ryName]) VALUES (3, N'Производство')\n" +
            "INSERT [dbo].[Cate;ry] ([Id], [Cate;ryName]) VALUES (4, N'Тестирование')\n" +
            "INSERT [dbo].[Cate;ry] ([Id], [Cate;ryName]) VALUES (5, N'Обеспечение (Прочие)')\n" +
            "INSERT [dbo].[Parameter] ([Id], [ParamName]) VALUES (1, N'Про')\n" +
            "INSERT [dbo].[Parameter] ([Id], [ParamName]) VALUES (2, N'Норм')\n" +
            "INSERT [dbo].[Parameter] ([Id], [ParamName]) VALUES (3, N'Бывалый')\n" +
            "INSERT [dbo].[Parameter] ([Id], [ParamName]) VALUES (4, N'Имитатор деятельности')\n" +
            "INSERT [dbo].[Resources] ([Id], [ResourceName]) VALUES (1, N'компьютер')\n" +
            "INSERT [dbo].[Resources] ([Id], [ResourceName]) VALUES (2, N'принтер')\n" +
            "INSERT [dbo].[Resources] ([Id], [ResourceName]) VALUES (3, N'прочее')\n" +
            "INSERT [dbo].[Summary] ([Id], [RecordId], [Cate;ryId], [ResourceId], [ParamId], [ActionId], [Value], [Notes]) VALUES (1, 1, 1, 1, 1, 1, 1.09, N'test')\n" +
            "INSERT [dbo].[Summary] ([Id], [RecordId], [Cate;ryId], [ResourceId], [ParamId], [ActionId], [Value], [Notes]) VALUES (2, 1, 2, 2, 2, 2, 3, N'тест')\n" +
            "INSERT [dbo].[Summary] ([Id], [RecordId], [Cate;ryId], [ResourceId], [ParamId], [ActionId], [Value], [Notes]) VALUES (3, 2, 3, 1, 2, 2, 6.99, N'тест')\n" +
            "ALTER TABLE [dbo].[Summary]  WITH CHECK ADD  CONSTRAINT [FK_Summary_Actions] FOREIGN KEY([ActionId])\n" +
            "REFERENCES [dbo].[Actions] ([Id])\n" +
            ";\n" +
            "ALTER TABLE [dbo].[Summary] CHECK CONSTRAINT [FK_Summary_Actions]\n" +
            ";\n" +
            "ALTER TABLE [dbo].[Summary]  WITH CHECK ADD  CONSTRAINT [FK_Summary_Cate;ry] FOREIGN KEY([Cate;ryId])\n" +
            "REFERENCES [dbo].[Cate;ry] ([Id])\n" +
            ";\n" +
            "ALTER TABLE [dbo].[Summary] CHECK CONSTRAINT [FK_Summary_Cate;ry]\n" +
            ";\n" +
            "ALTER TABLE [dbo].[Summary]  WITH CHECK ADD  CONSTRAINT [FK_Summary_Parameter] FOREIGN KEY([ParamId])\n" +
            "REFERENCES [dbo].[Parameter] ([Id])\n" +
            ";\n" +
            "ALTER TABLE [dbo].[Summary] CHECK CONSTRAINT [FK_Summary_Parameter]\n" +
            ";\n" +
            "ALTER TABLE [dbo].[Summary]  WITH CHECK ADD  CONSTRAINT [FK_Summary_Resources] FOREIGN KEY([ResourceId])\n" +
            "REFERENCES [dbo].[Resources] ([Id])\n" +
            ";\n" +
            "ALTER TABLE [dbo].[Summary] CHECK CONSTRAINT [FK_Summary_Resources]\n" +
            ";\n" +
            "USE [master]\n" +
            ";\n" +
            "ALTER DATABASE [DbSim] SET  READ_WRITE \n" +
            ";\n";
    }
}
